package com.music.mapper;

import com.music.pojo.Playlist;
import com.music.pojo.PlaylistExample;
import java.util.List;

import com.music.utils.PlayListInfo;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.type.JdbcType;


public interface PlaylistMapper {
    @SelectProvider(type=PlaylistSqlProvider.class, method="countByExample")
    long countByExample(PlaylistExample example);

    @DeleteProvider(type=PlaylistSqlProvider.class, method="deleteByExample")
    int deleteByExample(PlaylistExample example);

    @Delete({
        "delete from playlist",
        "where plid = #{plid,jdbcType=INTEGER}"
    })
    int deleteByPrimaryKey(Integer plid);

    @Insert({
        "insert into playlist (plid, plname, ",
        "uid, pic, desc, ",
        "create_time)",
        "values (#{plid,jdbcType=INTEGER}, #{plname,jdbcType=VARCHAR}, ",
        "#{uid,jdbcType=INTEGER}, #{pic,jdbcType=VARCHAR}, #{desc,jdbcType=VARCHAR}, ",
        "#{createTime,jdbcType=DATE})"
    })
    int insert(Playlist row);

    @InsertProvider(type=PlaylistSqlProvider.class, method="insertSelective")
    int insertSelective(Playlist row);

    @SelectProvider(type=PlaylistSqlProvider.class, method="selectByExample")
    @Results({
        @Result(column="plid", property="plid", jdbcType=JdbcType.INTEGER, id=true),
        @Result(column="plname", property="plname", jdbcType=JdbcType.VARCHAR),
        @Result(column="uid", property="uid", jdbcType=JdbcType.INTEGER),
        @Result(column="pic", property="pic", jdbcType=JdbcType.VARCHAR),
        @Result(column="desc", property="desc", jdbcType=JdbcType.VARCHAR),
        @Result(column="create_time", property="createTime", jdbcType=JdbcType.DATE)
    })
    List<Playlist> selectByExample(PlaylistExample example);

    @Select({
        "select",
        "plid, plname, uid, pic, desc, create_time",
        "from playlist",
        "where plid = #{plid,jdbcType=INTEGER}"
    })
    @Results({
        @Result(column="plid", property="plid", jdbcType=JdbcType.INTEGER, id=true),
        @Result(column="plname", property="plname", jdbcType=JdbcType.VARCHAR),
        @Result(column="uid", property="uid", jdbcType=JdbcType.INTEGER),
        @Result(column="pic", property="pic", jdbcType=JdbcType.VARCHAR),
        @Result(column="desc", property="desc", jdbcType=JdbcType.VARCHAR),
        @Result(column="create_time", property="createTime", jdbcType=JdbcType.DATE)
    })
    Playlist selectByPrimaryKey(Integer plid);

    @UpdateProvider(type=PlaylistSqlProvider.class, method="updateByExampleSelective")
    int updateByExampleSelective(@Param("row") Playlist row, @Param("example") PlaylistExample example);

    @UpdateProvider(type=PlaylistSqlProvider.class, method="updateByExample")
    int updateByExample(@Param("row") Playlist row, @Param("example") PlaylistExample example);

    @UpdateProvider(type=PlaylistSqlProvider.class, method="updateByPrimaryKeySelective")
    int updateByPrimaryKeySelective(Playlist row);

    @Update({
        "update playlist",
        "set plname = #{plname,jdbcType=VARCHAR},",
          "uid = #{uid,jdbcType=INTEGER},",
          "pic = #{pic,jdbcType=VARCHAR},",
          "desc = #{desc,jdbcType=VARCHAR},",
          "create_time = #{createTime,jdbcType=DATE}",
        "where plid = #{plid,jdbcType=INTEGER}"
    })
    int updateByPrimaryKey(Playlist row);

    @Select("SELECT pl.plid, pl.plname, pl.`desc`, pl.`pic`, pl.`create_time`, pl.`uid`, COUNT(uh.uhpid) AS subscription_count " +
            "FROM playlist AS pl " +
            "LEFT JOIN user_has_playlist AS uh ON pl.plid = uh.plid " +
            "LEFT JOIN playlist_category AS pc ON pl.plid = pc.plid " +
            "LEFT JOIN category AS c ON pc.cid = c.cid " +
            "WHERE pl.plname LIKE CONCAT('%', #{search}, '%') OR c.cname LIKE CONCAT('%', #{search}, '%') " +
            "GROUP BY pl.plid, pl.plname, pl.`desc`, pl.`pic`, pl.`create_time`, pl.`uid` " +
            "ORDER BY subscription_count DESC limit 0,5")
    @ResultMap("map")
    List<Playlist> queryByPlaylistNameOrClassify(String param);

    @SelectProvider(type=PlaylistSqlProvider.class, method="selectByExample")
    @Results({
            @Result(column="plid", property="plid", id=true),
            @Result(column="plname", property="plname"),
            @Result(column="pic", property="pic"),
            @Result(column="desc", property="desc"),
            @Result(column="create_time", property="createTime"),
            @Result(column = "uid",property = "userInfo",one = @One(select = "com.music.mapper.UserInfoMapper.findByUid")),
            @Result(column = "plid",property = "favorUsers",many = @Many(select = "com.music.mapper.UserInfoMapper.findByPlid"))
    })
    List<Playlist> findWithSongs(PlaylistExample example);


    @Select("select * from playlist where uid = #{uid}")
    @Results(id = "map",value = {
            @Result(column="plid", property="plid", id=true),
            @Result(column="plname", property="plname"),
            @Result(column="pic", property="pic"),
            @Result(column="desc", property="desc"),
            @Result(column="create_time", property="createTime"),
            @Result(column = "uid",property = "userInfo",one = @One(select = "com.music.mapper.UserInfoMapper.findByUid")),
            @Result(column = "plid",property = "songs",many = @Many(select = "com.music.mapper.SingleSongMapper.findByplid")),
            @Result(column = "plid",property = "favorUsers",many = @Many(select = "com.music.mapper.UserInfoMapper.findByPlid")),
            @Result(column = "plid",property = "categories",many = @Many(select = "com.music.mapper.CategoryMapper.findByPlid"))
    })
    List<Playlist> findWithSongsByUid(int uid);

    @Select("SELECT pl.plid, pl.plname, pl.`desc`, pl.`pic`, pl.`create_time`, pl.`uid`, COUNT(uh.uhpid) AS subscription_count " +
            "FROM playlist AS pl " +
            "LEFT JOIN user_has_playlist AS uh ON pl.plid = uh.plid " +
            "LEFT JOIN playlist_category AS pc ON pl.plid = pc.plid " +
            "LEFT JOIN category AS c ON pc.cid = c.cid " +
            "WHERE pl.plname LIKE CONCAT('%', #{search}, '%') OR c.cname LIKE CONCAT('%', #{search}, '%') " +
            "GROUP BY pl.plid, pl.plname, pl.`desc`, pl.`pic`, pl.`create_time`, pl.`uid` " +
            "ORDER BY subscription_count DESC")
    @ResultMap("map")
    List<Playlist> findPlaylistBySearch(String search);
    @Select("select * from playlist")
    List<Playlist> findAll();

    @Select("select * from playlist where plid in (select plid from playlist_and_music where mid = #{mid})")
    List<Playlist> findByMid(Integer mid);

    @Select("select * from playlist where uid = #{uid}")
    @Results(value = {
            @Result(column="plid", property="plid", id=true),
            @Result(column="plname", property="plname"),
            @Result(column="pic", property="pic"),
            @Result(column="desc", property="desc"),
            @Result(column="create_time", property="createTime"),
            @Result(column = "uid",property = "userInfo",one = @One(select = "com.music.mapper.UserInfoMapper.findByUid")),
            @Result(column = "plid",property = "songs",many = @Many(select = "com.music.mapper.SingleSongMapper.findByplid")),
            @Result(column = "plid",property = "categories",many = @Many(select = "com.music.mapper.CategoryMapper.findByPlid"))
    })
    List<Playlist> findByUid(Integer uid);

    @Select("select * from playlist where plid in (select plid from user_has_playlist where uid = #{uid})")
    @ResultMap("ufcssMap")
    List<Playlist> findUserFavoritePlayLists(Integer uid);

    @Delete("delete from playlist_and_music where mid = #{mid} and plid in (select plid from playlist where uid = #{uid})")
    void removeFavorite(@Param("mid") Integer mid,@Param("uid") Integer uid);

    //添加收藏
    @Insert("insert into playlist_and_music(mid,plid) values (#{mid},#{plid})")
    void addFavorite(@Param("mid") Integer mid,@Param("plid") Integer plid);

    //创建新歌单
    @Insert("insert into playlist(plname,uid,create_time) values (#{plname},#{uid},now())")
    void addNewPlayList(@Param("plname") String plname,@Param("uid") Integer uid);

    //用于同时创建新歌单并加入歌曲
    @Select("select max(plid) from playlist")
    Integer findMaxPlid();

    //用于歌单封面，默认加入的第一首歌的歌曲封面
    @Select("select pic from playlist where plid = #{plid}")
    String findPlayListPic(Integer plid);

    @Update("update playlist set pic = #{pic} where plid = #{plid}")
    void updatePlayListPic(@Param("pic") String pic,@Param("plid") Integer plid);

    //查询是否被收藏
    @Select("select count(*) from playlist_and_music where mid = #{mid} and plid in (select plid from playlist where uid = #{uid})")
    Integer findCountFavoriteSong(@Param("uid") Integer uid,@Param("mid") Integer mid);

    //根据类别名查找歌单按订阅数降序
    @Select("SELECT" +
            "  pl.plid," +
            "  pl.plname," +
            "  pl.`desc`," +
            "  pl.`pic`," +
            "  pl.`create_time`," +
            "  pl.`uid`," +
            "  sub.subscription_count FROM (SELECT pc.plid, COUNT(uh.uhpid) AS subscription_count FROM playlist_category AS pc INNER JOIN category AS c ON" +
            "      pc.cid = c.cid LEFT JOIN user_has_playlist AS uh ON pc.plid = uh.plid" +
            "    WHERE c.cname = #{category}" +
            "    GROUP BY pc.plid) AS sub INNER JOIN playlist AS pl ON pl.plid = sub.plid ORDER BY sub.subscription_count DESC")
    @Results(value = {
            @Result(column="plid", property="plid", id=true),
            @Result(column="plname", property="plname"),
            @Result(column="pic", property="pic"),
            @Result(column="desc", property="desc"),
            @Result(column="create_time", property="createTime"),
            @Result(column="subscription_count", property="subscription_count"),
            @Result(column = "uid",property = "userInfo",one = @One(select = "com.music.mapper.UserInfoMapper.findByUid")),
            @Result(column = "plid",property = "categories",many = @Many(select = "com.music.mapper.CategoryMapper.findByPlid")),
    })
    List<Playlist> findPlaylistByCategory(String category);
    //查找所有歌单按订阅数降序
    @Select("SELECT" +
            "  pl.plid," +
            "  pl.plname," +
            "  pl.`desc`," +
            "  pl.`pic`," +
            "  pl.`create_time`," +
            "  pl.`uid`," +
            "  sub.subscription_count FROM (SELECT pc.plid, COUNT(uh.uhpid) AS subscription_count FROM playlist_category AS pc INNER JOIN category AS c ON" +
            "      pc.cid = c.cid LEFT JOIN user_has_playlist AS uh ON pc.plid = uh.plid" +
            "    GROUP BY pc.plid) AS sub INNER JOIN playlist AS pl ON pl.plid = sub.plid ORDER BY sub.subscription_count DESC")
    @Results(value = {
            @Result(column="plid", property="plid", id=true),
            @Result(column="plname", property="plname"),
            @Result(column="pic", property="pic"),
            @Result(column="desc", property="desc"),
            @Result(column="create_time", property="createTime"),
            @Result(column="subscription_count", property="subscription_count"),
            @Result(column = "uid",property = "userInfo",one = @One(select = "com.music.mapper.UserInfoMapper.findByUid")),
            @Result(column = "plid",property = "categories",many = @Many(select = "com.music.mapper.CategoryMapper.findByPlid"))
    })
    List<Playlist> findPlaylistByCategoryAll();



    @Select("select * from playlist where plid = #{plid}")
    @Results(id = "ufcssMap",value = {
            @Result(column="plid", property="plid", id=true),
            @Result(column="plname", property="plname"),
            @Result(column="pic", property="pic"),
            @Result(column="desc", property="desc"),
            @Result(column="create_time", property="createTime"),
            @Result(column = "uid",property = "userInfo",one = @One(select = "com.music.mapper.UserInfoMapper.findByUid")),
            @Result(column = "plid",property = "favorUsers",many = @Many(select = "com.music.mapper.UserInfoMapper.findByPlid")),
            @Result(column = "plid",property = "categories",many = @Many(select = "com.music.mapper.CategoryMapper.findByPlid")),
            @Result(column = "plid",property = "subscription_count",one = @One(select = "com.music.mapper.PlaylistMapper.findPlayListFavoriterCount")),
            @Result(column = "plid",property = "songsNum",one = @One(select = "com.music.mapper.PlaylistMapper.findPlayListSongsCountByPlid")),
    })
    Playlist findByPlid(Integer plid);

    @Select("select count(*) from user_has_playlist where plid = #{plid}")
    Integer findPlayListFavoriterCount(Integer plid);


    @Select("select count(*) from user_has_playlist where plid = #{plid} and uid = #{uid}")
    Integer findisFavorite(@Param("plid") Integer plid,@Param("uid") Integer uid);

    @Select("select count(*) from playlist_and_music where plid = #{plid}")
    String findPlayListSongsCountByPlid(Integer plid);

    @Insert("insert into user_has_playlist (uid, plid) values (#{uid},#{plid})")
    void addFavoritePlayList(@Param("plid") Integer plid,@Param("uid") Integer uid);

    @Delete("delete from user_has_playlist where plid = #{plid} and uid = #{uid}")
    void removeFavoritePlayList(@Param("plid") Integer plid,@Param("uid") Integer uid);

    @Delete("delete from playlist where plid = #{plid}")
    void removePlayList(Integer plid);

    @Insert("insert into playlist(plname,uid,`desc`,pic,create_time) values (#{plname},#{uid},#{desc},#{pic},now())")
    void addNewPlayListWithDetail(@Param("plname") String plname,@Param("desc") String desc,@Param("pic") String picUrl,@Param("uid") Integer uid);

    @Insert("insert into playlist_category(plid,cid) values (#{plid},#{cate})")
    void addPlayListCateGory(@Param("cate") String cate,@Param("plid") Integer maxPlid);


    @Delete("delete from playlist_category where plid=#{plid}")
    void deletePlayListCateGoryByPlid(Integer plid);

    @UpdateProvider(type = UpdatePlayListSqlProvider.class, method = "updateActive")
    void updatePlayListInfoActive(@Param("playListInfo") PlayListInfo playListInfo);
}